using System;
using System.Data;
using System.Data.Common;
using System.Windows.Forms;
using System.Collections.Generic;
using System.ComponentModel;
using System.Text;

namespace Tilver.DBConnectionUI.Editors
{
    [ToolboxItem(false)]
    internal partial class SqlClientEditor : UserControl, IUDLEditor, IOleDbEditor
	{
        Dictionary<string, string> overrides = new Dictionary<string, string>(StringComparer.InvariantCultureIgnoreCase);
        System.Data.SqlClient.SqlConnectionStringBuilder _builder =
            new System.Data.SqlClient.SqlConnectionStringBuilder();
        List<string> _servers = new List<string>();
        bool serversListLoaded = false;
        string sqlExpressInstanceName = @".\SQLEXPRESS";

        public SqlClientEditor()
        {
            InitializeComponent();

            // wire event handlers
            this.txServer.Validated += delegate { _builder.DataSource = this.txServer.Text; };
            this.txServer.TextChanged += delegate { txServerDatabase.Enabled = (ckUseServerDatabase.Checked && !(string.IsNullOrEmpty(txServer.Text))); };
            this.txServer.DropDown += delegate { this.LoadServersList(); };
            this.txUserName.Validated += delegate { _builder.UserID = this.txUserName.Text; };
            this.txPassword.Validated += delegate { _builder.Password = this.txPassword.Text; };
            this.ckUseIntegratedSecurity.CheckedChanged += delegate { this.setSecurityMode(); };
            this.ckPersistSecurity.CheckedChanged += delegate { _builder.PersistSecurityInfo = this.ckPersistSecurity.Checked; };
            this.ckUseServerDatabase.CheckedChanged += delegate { this.setDatabaseMode(); };
            this.txServerDatabase.Validated += delegate { _builder.InitialCatalog = this.txServerDatabase.Text; };
            this.txServerDatabase.DropDown += delegate { this.LoadDatabasesList(); };
            this.txFileDatabasePath.Validated += delegate { _builder.AttachDBFilename = this.txFileDatabasePath.Text; };
            this.cmdFindFileDatabase.Click += delegate { this.FindFileDatabase(); };
            this.ckUseIntegratedSecurity.Checked = true;
        }

        #region IConnectionEditor Members

        public string ConnectionString
        {
            get
            {
                if (string.IsNullOrEmpty(oleProvider))
                    return _builder.ConnectionString;
                else
                {
                    //if is oledb provider we will have to change some key  names
                    StringBuilder sb = new StringBuilder();
                    DbConnectionStringBuilder csb = new DbConnectionStringBuilder();
                    csb.ConnectionString = _builder.ConnectionString;

                    sb.Append("Provider=" + oleProvider);
                    foreach(KeyValuePair<string,object> kvp in csb)
                    {
                        sb.Append("; ");
                        switch (kvp.Key.ToLower())
                        {
                            case "integrated security": 
                                if(_builder.IntegratedSecurity)
                                    sb.Append("Integrated Security=SSPI"); break;
                            case "user id": sb.Append("uid=" + kvp.Value); break;
                            case "password": sb.Append("pwd=" + kvp.Value); break;
                            default: sb.Append(kvp.Key +"=" + kvp.Value); break;
                                break;
                        }
                    }
                    return sb.ToString();
                }
            }
            set
            {
                try
                {
                    _builder.ConnectionString = value;
                    this.RefreshData();
                }
                catch (Exception) { }
            }
        }

        public void RefreshData()
        {
            this.txServer.Text = _builder.DataSource;
            if (_builder.IntegratedSecurity)
                this.ckUseIntegratedSecurity.Checked = true;
            else
            {
                this.ckUseNameAndPassword.Checked = true;
                this.txUserName.Text = _builder.UserID;
                this.txPassword.Text = _builder.Password;
                this.ckPersistSecurity.Checked = _builder.PersistSecurityInfo;
            }

            if (_builder.AttachDBFilename.Length > 0)
            {
                this.ckUseFileDatabase.Checked = true;
                this.txFileDatabasePath.Text = _builder.AttachDBFilename;
            }
            else
            {
                this.ckUseServerDatabase.Checked = true;
                this.txServerDatabase.Text = _builder.InitialCatalog;
            }
        }

        public object GetAdvancedPropertiesObject()
        {
            return _builder;
        }

        #endregion

        #region IOleDbEditor Members

        string oleProvider;
        public void SetOleProviderID(string invariantName)
        {
            oleProvider = invariantName;
            ckUseServerDatabase.Checked = true;
            ckUseFileDatabase.Enabled = false;
            overrides.Add("User Id", "uid");
            overrides.Add("Password", "pwd");
        }

        #endregion

        #region helper Methods

        void setSecurityMode()
        {
            bool integrated = this.ckUseIntegratedSecurity.Checked;
            if (integrated)
            {
                _builder.IntegratedSecurity = true;
                ResetProperty("User ID");
                ResetProperty("Password");
                ResetProperty("Persist Security Info");
            }
            else
                _builder.IntegratedSecurity = false;

            this.txUserName.Text = _builder.UserID;
            this.txPassword.Text = _builder.Password;
            this.ckPersistSecurity.Checked = _builder.PersistSecurityInfo;

            this.txUserName.Enabled = !integrated;
            this.txPassword.Enabled = !integrated;
            this.ckPersistSecurity.Enabled = !integrated;
        }

        void setDatabaseMode()
        {
            bool isServerDb = ckUseServerDatabase.Checked;
            if (isServerDb)
            {
                ResetProperty("Data Source");
                ResetProperty("AttachDbFilename");
                ResetProperty("User Instance");
            }
            else
            {
                _builder.UserInstance = true;
                _builder.DataSource = sqlExpressInstanceName;
                ResetProperty("Initial Catalog");
            }

            txServer.Text = _builder.DataSource;
            txServerDatabase.Text = _builder.InitialCatalog;
            txFileDatabasePath.Text = _builder.AttachDBFilename;

            txServer.Enabled = isServerDb;
            txServerDatabase.Enabled = isServerDb && txServer.Text.Trim().Length > 0;
            txFileDatabasePath.Enabled = !isServerDb;
            cmdFindFileDatabase.Enabled = txFileDatabasePath.Enabled;
        }

        void LoadServersList()
        {
            // enumerate the available servers the first time the combo is opened
            if (!serversListLoaded)
            {
                Cursor = Cursors.WaitCursor;

                DbDataSourceEnumerator enumerator =
                    System.Data.SqlClient.SqlClientFactory.Instance.CreateDataSourceEnumerator();

                DataTable table = enumerator.GetDataSources();
                if (table != null)
                {
                    foreach (DataRow row in table.Rows)
                    {
                        string name = row["ServerName"].ToString();
                        string inst = row["InstanceName"].ToString();

                        if (string.IsNullOrEmpty(inst))
                            txServer.Items.Add(name);
                        else
                            txServer.Items.Add(name + @"\" + inst);
                    }
                }
                serversListLoaded = true;

                Cursor = Cursors.Default;
            }

        }

        void LoadDatabasesList()
        {

            Cursor = Cursors.WaitCursor;

            DbProviderFactory factory = System.Data.SqlClient.SqlClientFactory.Instance;
            DbConnection conn = factory.CreateConnection();
            System.Data.SqlClient.SqlConnectionStringBuilder builder =
                new System.Data.SqlClient.SqlConnectionStringBuilder();

            builder.DataSource = txServer.Text;
            if (ckUseIntegratedSecurity.Checked)
                builder.IntegratedSecurity = true;
            else
            {
                builder.UserID = txUserName.Text;
                builder.Password = txPassword.Text;
            }
            conn.ConnectionString = builder.ConnectionString;

            DbCommand cmd = factory.CreateCommand();
            cmd.CommandText = "sp_databases";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = conn;
            try
            {
                conn.Open();
                using (DbDataReader rd = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (rd.Read())
                        this.txServerDatabase.Items.Add(rd[0]);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
            Cursor = Cursors.Default;
        }

        void FindFileDatabase()
        {
            OpenFileDialog fd = new OpenFileDialog();
            fd.Filter = "SQL Server Databases *.mdf|*.mdf|All Files|*.*";
            if (fd.ShowDialog(this.Parent) == DialogResult.OK)
            {
                _builder.AttachDBFilename = fd.FileName;
                this.txFileDatabasePath.Text = fd.FileName;
            }
        }

        void SetValue(string key, object value)
        {
            if (_builder.ContainsKey(key))
                _builder[key] = value;
        }

        object GetValue(string key)
        {
            if (_builder.ContainsKey(key))
                return _builder[key];
            return null;
        }

        void ResetProperty(string key)
        {
            if (_builder.ContainsKey(key))
                _builder[key] = null;
            _builder.Remove(key);
        }

        private object DefaultValue(PropertyDescriptor pd)
        {
            foreach (Attribute att in pd.Attributes)
            {
                if (att is DefaultValueAttribute)
                    return ((DefaultValueAttribute)att).Value;
            }
            return null;
        }

        #endregion


    }
}
